package aidansmeaton.airport.database;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Arrays;

import aidansmeaton.airport.structures.Airport;
import android.app.ProgressDialog;
import android.content.Context;
import android.content.res.AssetManager;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.AsyncTask;
import android.util.Log;

/**
 * Copies an already existing SQLite database into this
 * application. Provides a singleton instance to a
 * database helper which provides cursors to the database.
 * @see http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/
 * 
 * @author Juan-Manuel Flux�
 * 
 * @author Aidan Smeaton (queries)
 */
public class DatabaseHelper extends SQLiteOpenHelper {
	
	// Database table columns
	public static final int AIRPORT_ID = 0;
	public static final int AIRPORT_IATA = 1;
	public static final int AIRPORT_NAME = 2;
	public static final int AIRPORT_CITY = 3;
	public static final int AIRPORT_COUNTRY = 4;
	public static final int AIRPORT_LATITUDE = 5;
	public static final int AIRPORT_LONGITUDE = 6;
	public static final int AIRPORT_RUNWAY = 7;
	public static final int AIRPORT_PASSENGERS = 8;
	
	// Android's default system path of the application database
	private static final String DB_PATH = "/data/data/aidansmeaton.airport/databases/";
	private static final String DB_NAME = "1.db";
	
	private static DatabaseHelper instance;
	private static boolean exists;
	private SQLiteDatabase db;
	private final Context context;
	
	private DatabaseHelper(Context context) {
		super(context, DB_NAME, null, 1);
		this.context = context;
		
		exists = checkDatabaseExists();
	}
	
	/**
	 * Returns the singleton DataBaseHelper, instantiating
	 * if not yet done so.
	 * @param context Application context.
	 * @return The DataBaseHelper singleton.
	 */
	public static DatabaseHelper getInstance(Context context) {
		if (instance == null) {
			instance = new DatabaseHelper(context);
			
			try {
				instance.createDatabase();
			} catch (IOException ioe) {
				/* Critical error - database could not be created. This
				 * is probably due to lack of space.
				 */
				throw new Error("Unable to create database");
			}
			
			if (!exists) {
				return null;
			}
		}
		
		return instance;
	}
	
	/**
	 * Creates an empty database on the system and
	 * populates it from a pre-prepared database.
	 */
	private void createDatabase() throws IOException{
		if(checkDatabaseExists()){
			/* Database exists, so we can just open it */
			instance.openDatabase();
		} else {
			/* Database does not exist - create a new one */
			this.getReadableDatabase();
			
			/* Set up an AsyncTask to go off and copy our
			 * pre-prepared database into the data
			 * section of this application.
			 */
			CreateDatabaseTask createDatabaseTask = new CreateDatabaseTask();
			createDatabaseTask.execute(0);
		}
	}

	/**
	 * Checks if the database already exists.
	 */
	public static boolean checkDatabaseExists() {
		SQLiteDatabase checkDB = null;

		/* Opening the database will fail if it doesn't
		 * exist, so we can catch and ignore that error.
		 */
		try {
			String myPath = DB_PATH + DB_NAME;
			checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
		} catch (SQLiteException e) {
			/* Database does't exist yet - we can
			 * ignore this exception. This function
			 * is not for creating the database.
			 */
		}

		if (checkDB != null) {
			checkDB.close();
		}

		return checkDB != null ? true : false;
	}

	/**
	 * Copies each database file assets-folder to a single file
	 * in the data storage for this app.
	 * 
	 * @see http://stackoverflow.com/questions/2860157/load-files-bigger-than-1m-from-assets-folder/3093966#3093966
	 * @author Seva Alekseyev
	 */
	private void copyDatabase() throws IOException{
		File dbFile = new File(DB_PATH + DB_NAME);
		AssetManager am = context.getAssets();
		OutputStream os = new FileOutputStream(dbFile);
		dbFile.createNewFile();
		
		byte[] b = new byte[1024];
		int i, r;
		
		String[] Files = am.list("");
		Arrays.sort(Files);
		
		for (i = 1; i < 10; i++) {
			String fn = String.format("%d.db", i);
			if (Arrays.binarySearch(Files, fn) < 0)
				break;
			InputStream is = am.open(fn);
			while ((r = is.read(b)) != -1)
				os.write(b, 0, r);
			is.close();
		}
		os.close();
	}

	/**
	 * Attempts to open the database in read-write mode.
	 */
	private void openDatabase() throws SQLException{
		String myPath = DB_PATH + DB_NAME;
		db = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
	}

	/**
	 * Close the open database.
	 */
	@Override
	public synchronized void close() {
		if (db != null)
			db.close();

		super.close();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		
	}

	/* Everything which follows is mostly query related. */
	
	
	/**
	 * Returns a cursor to a single bus stop, searching
	 * by the stop code.
	 * 
	 * @param stopCode Unique code of a bus stop.
	 * 
	 * @return Cursor to the bus stop.
	 */
	
	public Cursor getPlanes() {
		return db.rawQuery("SELECT * FROM plane", null);
	}
	
	public Airport getAirport(String iata) {
		Cursor c = db.rawQuery("SELECT * FROM airport a WHERE a.iata = '" + iata + "'", null);
		c.moveToFirst();
		return new Airport(c);
	}
	
	public Cursor getAirports() {
		return db.rawQuery("SELECT * FROM airport a WHERE a.country = 'United Kingdom'", null);
	}
	
	public Cursor getAirport(String name, String country) {
		return db.rawQuery("SELECT * FROM airport a WHERE a.airport_name = '" + name + "' AND a.country = '" + country + "'", null);
	}
	
	public Cursor getAirports(int passengers) {
		
		switch (passengers){
		case 0: return db.rawQuery("SELECT * FROM airport a " +
				"WHERE a.passengers < 3000", null);
		case 1: return db.rawQuery("SELECT * FROM airport a " +
				"WHERE a.passengers >= 3000 " +
				"AND a.passengers < 30000 ", null);
		case 2: return db.rawQuery("SELECT * FROM airport a " +
				"WHERE a.passengers >= 30000 " +
				"AND a.passengers < 300000 ", null);
		case 3: return db.rawQuery("SELECT * FROM airport a " +
				"WHERE a.passengers >= 300000 " +
				"AND a.passengers < 3000000 ", null);
		case 4: return db.rawQuery("SELECT * FROM airport a " +
				"WHERE a.passengers >= 3000000 " +
				"AND a.passengers < 30000000 ", null);
		case 5: return db.rawQuery("SELECT * FROM airport a " +
				"WHERE a.passengers >= 30000000 ", null);
		}
		return null;
	}
	
	public Cursor getAirports(int passengers, String country) {
		
		switch (passengers){
		case 0: return db.rawQuery("SELECT * FROM airport a " +
				"WHERE a.passengers < 3000 " +
				"AND runway > 0 " +
				"AND a.country = '" + country + "'", null);
		case 1: return db.rawQuery("SELECT * FROM airport a " +
				"WHERE a.passengers >= 3000 " +
				"AND a.passengers < 30000 " +
				"AND runway > 0 " +
				"AND a.country = '" + country + "'", null);
		case 2: return db.rawQuery("SELECT * FROM airport a " +
				"WHERE a.passengers >= 30000 " +
				"AND a.passengers < 300000 " +
				"AND runway > 0 " +
				"AND a.country = '" + country + "'", null);
		case 3: return db.rawQuery("SELECT * FROM airport a " +
				"WHERE a.passengers >= 300000 " +
				"AND a.passengers < 3000000 " +
				"AND runway > 0 " +
				"AND a.country = '" + country + "'", null);
		case 4: return db.rawQuery("SELECT * FROM airport a " +
				"WHERE a.passengers >= 3000000 " +
				"AND a.passengers < 30000000 " +
				"AND runway > 0 " +
				"AND a.country = '" + country + "'", null);
		case 5: return db.rawQuery("SELECT * FROM airport a " +
				"WHERE a.passengers >= 30000000 " +
				"AND runway > 0 " +
				"AND a.country = '" + country + "'", null);
		}
		return null;
	}
	
	
	public Cursor getAirportsInBoundary(int passengers, double lat1, double lat2, double lon1, double lon2) {
		switch (passengers){
		case 0: return db.rawQuery("SELECT * FROM airport a " +
				"WHERE a.passengers < 3000 " +
				"AND a.latitude >= " + lat1 +
				" AND a.latitude <= " + lat2 +
				" AND a.longitude >= " + lon1 +
				" AND a.longitude <= " + lon2
				, null);		
		case 1: return db.rawQuery("SELECT * FROM airport a " +
				"WHERE a.passengers >= 3000 " +
				"AND a.passengers < 30000 " +
				"AND a.latitude >= " + lat1 +
				" AND a.latitude <= " + lat2 +
				" AND a.longitude >= " + lon1 +
				" AND a.longitude <= " + lon2
				, null);		
		case 2: return db.rawQuery("SELECT * FROM airport a " +
				"WHERE a.passengers >= 30000 " +
				"AND a.passengers < 300000 " +
				"AND a.latitude >= " + lat1 +
				" AND a.latitude <= " + lat2 +
				" AND a.longitude >= " + lon1 +
				" AND a.longitude <= " + lon2
				, null);		
		case 3: return db.rawQuery("SELECT * FROM airport a " +
				"WHERE a.passengers >= 300000 " +
				"AND a.passengers < 3000000 " +
				"AND a.latitude >= " + lat1 +
				" AND a.latitude <= " + lat2 +
				" AND a.longitude >= " + lon1 +
				" AND a.longitude <= " + lon2
				, null);		
		case 4: Log.w("HELLO!","SELECT * FROM airport a " +
				"WHERE a.passengers >= 3000000 " +
				"AND a.latitude >= " + lat1 +
				" AND a.latitude <= " + lat2 +
				" AND a.longitude >= " + lon1 +
				" AND a.longitude <= " + lon2);
				return db.rawQuery("SELECT * FROM airport a " +
				"WHERE a.passengers >= 3000000 " +
				"AND a.passengers < 30000000 " +
				"AND a.latitude >= " + lat1 +
				" AND a.latitude <= " + lat2 +
				" AND a.longitude >= " + lon1 +
				" AND a.longitude <= " + lon2
				, null);		
		case 5: Log.w("HELLO!","SELECT * FROM airport a " +
				"WHERE a.passengers >= 30000000 " +
				"AND a.latitude >= " + lat1 +
				" AND a.latitude <= " + lat2 +
				" AND a.longitude >= " + lon1 +
				" AND a.longitude <= " + lon2);
				return db.rawQuery("SELECT * FROM airport a " +
				"WHERE a.passengers >= 30000000 " +
				"AND a.latitude >= " + lat1 +
				" AND a.latitude <= " + lat2 +
				" AND a.longitude >= " + lon1 +
				" AND a.longitude <= " + lon2
				, null);
		
		}
		return null;
	}
	

	
	/*
	 * AsyncTask to create database on first run.
	 */
	private class CreateDatabaseTask extends AsyncTask<Integer, Integer, Boolean> {
		ProgressDialog dialog;
		
		/**
		 * Show a progress dialog before beginning the task.
		 * 
		 * @see android.os.AsyncTask#onPreExecute()
		 */
		@Override
		protected void onPreExecute() {
			dialog = ProgressDialog.show(context,"Preparing database",
					"Please wait...", true);
		}
		
		/**
		 * Copy the database from its raw asset files into
		 * an actual SQLite database.
		 */
		@Override
		protected Boolean doInBackground(Integer... args) {
			try {
				copyDatabase();
			} catch (IOException e) {
				/* An error occurred while copying the database */
				e.printStackTrace();
			}
			
			return true;
		}
		
		/**
		 * After the task has complete, dismiss the progress
		 * dialog and open the database.
		 */
		@Override
		protected void onPostExecute(Boolean result) {
			dialog.dismiss();
			
			exists = true;
			instance.openDatabase();
		}
	}
}